package com.example.dbutils;

import com.example.bean.User;
import com.example.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

// commons-dbutils下的QueryRunner类功能使用
public class QueryRunnerTest {

    @Test
    public void testInsert() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            QueryRunner queryRunner = new QueryRunner();
            String sql = "insert into user_table(user, password) values(?, ?)";
            int result = queryRunner.update(connection, sql, "jack", "jack123");
            if (result > 0) {
                System.out.println("插入成功");
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 查询单条记录
    @Test
    public void testQuery1() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            QueryRunner queryRunner = new QueryRunner();
            String sql = "select user, password, balance from user_table where user = ?";
            BeanHandler<User> beanHandler = new BeanHandler<>(User.class);
            User user = queryRunner.query(connection, sql, beanHandler, "jack");
            System.out.println(user);
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 查询多条记录
    @Test
    public void testQuery2() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            QueryRunner queryRunner = new QueryRunner();
            String sql = "select user, password, balance from user_table";
            BeanListHandler<User> beanListHandler = new BeanListHandler<>(User.class);
            List<User> users = queryRunner.query(connection, sql, beanListHandler);
            users.forEach(System.out::println);
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 将结果查询到Map中
    @Test
    public void testQuery3() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            QueryRunner queryRunner = new QueryRunner();
            String sql = "select user, password, balance from user_table where user = ?";
            MapHandler mapHandler = new MapHandler();
            Map<String, Object> map = queryRunner.query(connection, sql, mapHandler, "jack");
            System.out.println(map);
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 将结果查询到Map中
    @Test
    public void testQuery4() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            QueryRunner queryRunner = new QueryRunner();
            String sql = "select user, password, balance from user_table";
            MapListHandler mapListHandler = new MapListHandler();
            List<Map<String, Object>> mapList = queryRunner.query(connection, sql, mapListHandler);
            mapList.forEach(System.out::println);
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 查询count、max等特殊值
    @Test
    public void testQuery5() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            QueryRunner queryRunner = new QueryRunner();
            String sql = "select count(*) from user_table";
            ScalarHandler<Long> scalarHandler = new ScalarHandler<>();
            Long count = queryRunner.query(connection, sql, scalarHandler);
            System.out.println(count);

            String sql1 = "select max(birth) from customers";
            ScalarHandler<Date> scalarHandler1 = new ScalarHandler<>();
            Date date = queryRunner.query(connection, sql1, scalarHandler1);
            System.out.println(date);
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 自定义ResultSetHandler
    @Test
    public void testQuery6() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            QueryRunner queryRunner = new QueryRunner();
            String sql = "select user, password, balance from user_table";
            ResultSetHandler<User> resultSetHandler = new ResultSetHandler<User>() {
                @Override
                public User handle(ResultSet resultSet) throws SQLException {
                    System.out.println("处理用户信息");
                    if (resultSet.next()) {
                        User user = new User();
                        user.setUser(resultSet.getString("user"));
                        user.setPassword("*****");
                        user.setBalance(resultSet.getInt("balance"));
                        return user;
                    }
                    return null;
                }
            };
            User user = queryRunner.query(connection, sql, resultSetHandler);
            System.out.println(user);

        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }


}
